﻿Imports DTO

Public Class AcquyDAO
    Private ketnoi As ConnectDB

    Public Sub New()
        ketnoi = New ConnectDB()
    End Sub

    Public Function InsertAcQuy(ByVal aq As AcquyDTO, ByVal flagIsUp As Integer)
        Dim LAcQuy As Integer = aq.MaAcQuy
        Dim TAcQuy As String = aq.TenAcQuy
        Dim MLoai As Integer = aq.MaLoai
        Dim HSX As String = aq.HangSanXuat
        Dim DAp As Single = aq.DienAp
        Dim DDien As Single = aq.DongDien
        Dim Gia As Integer = aq.Gia
        Dim sqlString As String
        If flagIsUp = 1 Then
            sqlString = "INSERT ACQUY(TenAcQuy,MaLoai,HangSanXuat,DongDien,DienAp,Gia) VALUES(N'" + TAcQuy.ToString() + "'," + MLoai.ToString() + ",N'" + HSX.ToString() + "'," + DDien.ToString() + "," + DAp.ToString() + ", " + Gia.ToString() + ")"
        ElseIf flagIsUp = 2 Then
            sqlString = "UPDATE ACQUY SET TenAcQuy =N'" + TAcQuy.ToString() + _
                                                    "',MaLoai = " + MLoai.ToString() + _
                                                    ",HangSanXuat = N'" + HSX.ToString() + _
                                                    "', DienAp = " + DAp.ToString() + _
                                                    ", DongDien = " + DDien.ToString() + _
                                                    ", Gia = " + Gia.ToString() + " WHERE MaAcQuy = " + LAcQuy.ToString() + ""
        End If
        Return ketnoi.Execute(sqlString)
    End Function

    Public Function LoadComboListbox()
        Dim dt As DataTable
        dt = ketnoi.GetData("Select * From LOAIACQUY")
        Return dt
    End Function

    Public Function GetData()
        Dim MAcQuy As Integer
        Dim TAcQuy As String
        Dim MLoai As Integer
        Dim HSX As String
        Dim DAp As Single
        Dim DDien As Single
        Dim Gia As Integer
        Dim laq As AcquyDTO
        Dim laqList As New List(Of AcquyDTO)
        Dim dt As DataTable
        dt = ketnoi.GetData("Select * From ACQUY")
        Dim i As Integer
        For i = 0 To dt.Rows.Count - 1
            MAcQuy = Val(dt.Rows(i)("MaAcQuy"))
            TAcQuy = dt.Rows(i)("TenAcQuy").ToString
            MLoai = Val(dt.Rows(i)("MaLoai"))
            HSX = dt.Rows(i)("HangSanXuat").ToString
            DAp = Val(dt.Rows(i)("DienAp"))
            DDien = Val(dt.Rows(i)("DongDien"))
            Gia = Val(dt.Rows(i)("Gia"))
            laq = New AcquyDTO(MAcQuy, TAcQuy, MLoai, HSX, DDien, DAp, Gia)
            laqList.Add(laq)
        Next
        Return laqList
    End Function

    Public Function TimKiem(ByVal aq As AcquyDTO)
        Dim sqlString As String
        Dim MAcQuy As Integer = aq.MaAcQuy
        Dim TAcQuy As String = aq.TenAcQuy
        Dim MLoai As Integer = aq.MaLoai
        Dim HSX As String = aq.HangSanXuat
        Dim DDien As Single = aq.DongDien
        Dim DDienMin As Single = aq.DongDienMin
        Dim DDienMax As Single = aq.DongDienMax
        Dim DAp As Single = aq.DienAp
        Dim DApMin As Single = aq.DienApMin
        Dim DApMax As Single = aq.DienApMax
        Dim Gia As Single = aq.Gia
        Dim GiaMin As Integer = aq.GiaMin
        Dim GiaMax As Integer = aq.GiaMax

        If aq.MaAcQuy <> 0 Then
            sqlString = "Select * From ACQUY WHERE MaAcQuy = " + aq.MaAcQuy.ToString() + " "
        Else
            sqlString = "Select * From ACQUY WHERE "
            Dim many As Boolean = False
            If TAcQuy <> "" Then
                sqlString += "TenAcQuy Like '%" + TAcQuy.ToString() + "%' "
                If many = False Then
                    many = True
                End If
            End If
            If HSX <> "" Then
                If many = True Then
                    sqlString += " and "

                End If
                If many = False Then
                    many = True
                End If
                sqlString = sqlString + " HangSanXuat Like '%" + HSX.ToString() + "%' "
                If many = False Then
                    many = True
                End If
            End If
            If MLoai <> 0 Then
                If many = True Then
                    sqlString += " and "
                End If
                sqlString = sqlString + "MaLoai Like '%" + MLoai.ToString() + "%' "
                If many = False Then
                    many = True
                End If
            End If
            If DDienMin <> 0 Then
                If many = True Then
                    sqlString += " and "
                End If
                sqlString = sqlString + "DongDien >= '" + DDienMin.ToString() + "'"
                If many = False Then
                    many = True
                End If
            End If
            If DDienMax <> 0 Then

                If many = True Then
                    sqlString += " and "
                End If
                sqlString = sqlString + "DongDien <= '" + DDienMax.ToString() + "'"
                If many = False Then
                    many = True
                End If
            End If
            If DDienMin <> 0 And DDienMax <> 0 Then
                If many = True Then
                    sqlString += " and "
                End If
                sqlString = sqlString + "DongDien >= '" + DDienMin.ToString() + "' And DongDien <= '" + DDienMax.ToString() + "' "
                If many = False Then
                    many = True
                End If
            End If
            If DApMin <> 0 Then
                If many = True Then
                    sqlString += " and "
                End If
                sqlString = sqlString + "DienAp >= '" + DApMin.ToString() + "'"
                If many = False Then
                    many = True
                End If
            End If
            If DApMax <> 0 Then
                If many = True Then
                    sqlString += " and "
                End If
                sqlString = sqlString + "DienAp <= '" + DApMax.ToString + "'"
                If many = False Then
                    many = True
                End If
            End If
            If DApMin <> 0 And DApMax <> 0 Then
                If many = True Then
                    sqlString += " and "
                End If
                sqlString = sqlString + "DienAp <= '" + DApMax.ToString() + "' And DienAp >= '" + DApMin.ToString() + "' "
                If many = False Then
                    many = True
                End If
            End If
            If GiaMin <> 0 Then
                If many = True Then
                    sqlString += " and "
                End If
                sqlString = sqlString + "Gia >= '" + GiaMin.ToString() + "'"
                If many = False Then
                    many = True
                End If
            End If
            If GiaMax <> 0 Then
                If many = True Then
                    sqlString += " and "
                End If
                sqlString = sqlString + "Gia <= '" + GiaMax.ToString() + "'"
                If many = False Then
                    many = True
                End If
            End If
            If GiaMin <> 0 And GiaMax <> 0 Then
                If many = True Then
                    sqlString += " and "
                End If
                sqlString = sqlString + "Gia <= '" + GiaMax.ToString() + "' And Gia >= '" + GiaMin.ToString() + "' "
                If many = False Then
                    many = True
                End If
            End If

        End If

        'sqlString = "Select * From ACQUY WHERE MaAcQuy Like '%" + MAcQuy + "%' or TenAcQuy Like '" + TAcQuy + "' or HangSanXuat Like '" + HSX + "' or MaLoai Like '" + MLoai + "' or '" + DAp + "' "
        Dim laq As AcquyDTO
        Dim aqList As New List(Of AcquyDTO)
        Dim dt As DataTable
        dt = ketnoi.GetData(sqlString)
        Dim i As Integer
        For i = 0 To dt.Rows.Count - 1
            MAcQuy = Val(dt.Rows(i)("MaAcQuy"))
            TAcQuy = dt.Rows(i)("TenAcQuy").ToString
            MLoai = Val(dt.Rows(i)("MaLoai"))
            HSX = dt.Rows(i)("HangSanXuat").ToString
            DDien = Val(dt.Rows(i)("DongDien"))
            DAp = Val(dt.Rows(i)("DienAp"))
            Gia = Val(dt.Rows(i)("Gia"))
            laq = New AcquyDTO(MAcQuy, TAcQuy, MLoai, HSX, DDien, DAp, Gia)
            aqList.Add(laq)
        Next
        Return aqList
    End Function

    Public Function DeleteAcQuy(ByVal id As Integer)
        Dim sqlstring As String
        sqlstring = "DELETE ACQUY WHERE MaAcQuy = " + id.ToString() + ""
        Return ketnoi.Execute(sqlstring)
    End Function

    Public Function SelectByID(ByVal id As Integer)
        Dim MAcQuy As Integer
        Dim TAcQuy As String
        Dim MLoai As Integer
        Dim HSX As String
        Dim DAp As Single
        Dim DDien As Single
        Dim Gia As Integer
        Dim laq As AcquyDTO
        Dim laqList As New List(Of AcquyDTO)
        Dim dt As DataTable
        dt = ketnoi.GetData("Select * From ACQUY Where MaAcQuy = " + id.ToString() + "")
        Dim i As Integer
        For i = 0 To dt.Rows.Count - 1
            MAcQuy = Val(dt.Rows(i)("MaAcQuy"))
            TAcQuy = dt.Rows(i)("TenAcQuy").ToString
            MLoai = Val(dt.Rows(i)("MaLoai"))
            HSX = dt.Rows(i)("HangSanXuat").ToString
            DAp = Val(dt.Rows(i)("DienAp"))
            DDien = Val(dt.Rows(i)("DongDien"))
            Gia = Val(dt.Rows(i)("Gia"))
            laq = New AcquyDTO(MAcQuy, TAcQuy, MLoai, HSX, DDien, DAp, Gia)
            laqList.Add(laq)
        Next
        Return laqList
    End Function

    Public Function SelectNameLoai(ByVal id As Integer)
        Dim MLoai As Integer
        Dim TLoai As String
        Dim laq As LoaiAcquyDTO
        Dim laqList As New List(Of LoaiAcquyDTO)
        Dim dt As DataTable
        dt = ketnoi.GetData("Select * From LOAIACQUY Where MaLoai = " + id.ToString() + "")
        Dim i As Integer
        For i = 0 To dt.Rows.Count - 1
            MLoai = Val(dt.Rows(i)("MaLoai"))
            TLoai = dt.Rows(i)("TenLoai").ToString()
            laq = New LoaiAcquyDTO(MLoai, TLoai)
            laqList.Add(laq)
        Next
        Return laqList
    End Function
End Class
